﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
     
namespace BabyShop.DAO
{
    public class Order
    {
        public static DataTable getAll()
        {
            string sql = "select users.fullname,orders.* from orders join users on users.id = orders.user_id where orders.deleted = 0";
            return DB_Provider.Read(sql);
        }

        public static DataTable find(string fullname,double total, int order_status)
        {            
            string sql = "select users.fullname,orders.* from orders join users on users.id = orders.user_id where fullname like @fullname and total >= @total and order_status=@order_status and orders.deleted = 0";
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = "%" + fullname + "%";
            da.SelectCommand.Parameters.Add("@total", SqlDbType.Float).Value = total;
            da.SelectCommand.Parameters.Add("@order_status", SqlDbType.TinyInt).Value = order_status;
            da.Fill(dt);
            return dt;
        }

        public static DataSet find(int id)
        {
            string sql = "select orders.*,users.fullname from orders join users on users.id = orders.user_id where orders.id=@id and orders.deleted = 0";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
            da.Fill(ds,"Orders");

            sql = "select order_details.id,products.name,order_details.quantity,order_details.price from order_details join products on products.id = order_details.product_id where order_id=@id";
            da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
            da.Fill(ds, "Order_Details");
            return ds;
        }

        public static void Insert(string name)
        {
            SqlDataAdapter da = getAdapter();
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            //da.Fill(dt);
            DataRow dr = dt.NewRow();
            dr["name"] = name;
            dr["deleted"] = 0;
            dt.Rows.Add(dr);
            da.Update(dt);            
        }

        public static void Update(int id, string name)
        {
            SqlDataAdapter da = getAdapter();
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);
            DataRow dr = dt.Rows.Find(id);
            dr["name"] = name;            
            da.Update(dt);
        }

        public static void Delete(int id)
        {
            SqlDataAdapter da = getAdapter();
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);
            DataRow dr = dt.Rows.Find(id);
            dr["deleted"] = 1;
            da.Update(dt);
        }

        public static SqlDataAdapter getAdapter()
        {
            string sql = "select * from orders where deleted = 0";
            return DB_Provider.getAdapter(sql);
        }
    }
}